import os,sys,getopt
from pathlib import Path
import pandas as pd
import random
from lxml import html
import openpyxl
import time
import listingUtil as util
from parseSKUCode import parseColorSizeCode,parseImageFileName

# 命令行参数处理
g_basedir = '' # 数据文件夹名称
g_brand = '' # 为哪个品牌名生成资料表
g_country = '' # 为哪个国家的店铺生成资料表
opts, args = getopt.getopt(sys.argv[1:], 'd:b:c:')
if len(opts)!=3:
    print(f"Usage：脚本 -d <数据目录> -b <品牌名称> -c <区域店铺代码>\n")
    exit()
else:
    for i in opts:
        if i[0]=='-d':
            g_basedir = i[1]
        elif i[0]=='-b':
            g_brand = i[1]
        elif i[0]=='-c':
            g_country = i[1]
        else:
            print(f'无法解析的参数:{i[0]} {i[1]}')

if not os.path.isdir(g_basedir):
    print(f"basedir不是文件夹. dir={g_basedir}")
    exit()
g_brand = g_brand.upper()
g_country = g_country.upper()

# 添加listing的模板字段名称
col_names_additem = {
    'SPU' : '', 'ColorCode' : '', 'SKU' : '', 'Product ID Type' : 'EAN', 'Product ID' : '', 'Product Name' : '', 'Brand' : g_brand, 'Selling Price' : '', 'Shipping Weight (lbs)' : '',
    'Site Description' : '', 'Main Image URL' : '', 'Additional Image URL (+)' : '', 'Additional Image URL 1 (+)' : '', 'Additional Image URL 2 (+)' : '',
    'Additional Image URL 3 (+)' : '', 'Additional Image URL 4 (+)' : '', 'Additional Image URL 5 (+)' : '',
    'California Prop 65 Warning Text' : 'None', 'Small Parts Warning Code (+)' : '0 - No warning applicable', 'Country of Origin - Textiles' : 'Imported',
    'Gender' : 'Female', 'Clothing Size' : '', 'Age Group (+)' : '', 'Color (+)' : '', 'Color Category (+)' : '', 'Key Features (+)' : '', 'Key Features 1 (+)' : '', 'Key Features 2 (+)' : '',
    'Clothing Size Group' : '', 'Clothing Style (+)' : '', 'Fabric Material Name' : '', 'Fabric Material Percentage' : '', 'Fabric Material Name 1' : '', 'Fabric Material Percentage 1' : '',
    'Fabric Material Name 2' : '', 'Fabric Material Percentage 2' : '', 'Fabric Care Instructions (+)' : '', 'Material (+)' : '', 'Pattern (+)' : '', 'Clothing Cut (+)' : '', 'Clothing Fit' : '',
    'Clothing Weight' : '', 'Clothing Top Style (+)' : '', 'Clothing Length Style' : '', 'MSRP' : '', 'Manufacturer Name' : '', 'Manufacturer Part Number' : '', 'Model Number' : '',
    'Count Per Pack' : '', 'Total Count' : '', 'Number of Pieces' : '', 'Is Set' : '', 'Dress Shirt Size' : '', 'Measure' : '', 'Unit' : '', 'Clothing Neck Style' : '',
    'Measure.1' : '', 'Unit.1' : '', 'Sleeve Length Style' : '', 'Sleeve Style' : '', 'Collar Style' : '', 'T-Shirt Type (+)' : '', 'Jacket Style (+)' : '', 'Suit Breasting Style' : '',
    'Sweater Style (+)' : '', 'Scarf Style (+)' : '', 'Hat Style (+)' : '', 'Hat Size Measurement' : '', 'Bra Style (+)' : '', 'Bra Size' : '', 'Measure.2' : '', 'Unit.2' : '', 'Bra Cup Size' : '',
    'Upper Body Strap Configuration (+)' : '', 'Measure.3' : '', 'Unit.3' : '', 'Panty Size' : '', 'Waist Rise' : '', 'Waist Style (+)' : '', 'Measure.4' : '', 'Unit.4' : '', 'Measure.5' : '',
    'Unit.5' : '', 'Leg Opening Cut' : '', 'Pant Leg Cut' : '', 'Measure.6' : '', 'Unit.6' : '', 'Jean Style (+)' : '', 'Jean Wash' : '', 'Jean Finish (+)' : '', 'Pant Size' : '', 'Pant Fit (+)' : '',
    'Pant Style' : '', 'Belt Style (+)' : '', 'Belt Buckle Style' : '', 'Shorts Style (+)' : '', 'Skirt Style (+)' : '', 'Skirt Length Style' : '', 'Hosiery Style (+)' : '', 'Sheerness' : '',
    'Underpant/Swim Bottom Style' : '', 'Underwear Style (+)' : '', 'Underpants Type' : '', 'Sock Size' : '', 'Sock Style' : '', 'Sock Rise' : '', 'Shoe Size' : '', 'Accent Color' : '',
    'Closure Type' : '', 'Swimsuit Style' : '', 'Dress Style' : '', 'Pajama Type' : '', 'Outerwear Coat Jacket and Vest Type' : '', 'GOTS Certification' : '', 'Theme (+)' : 'Fashion', 
    'Occasion (+)' : 'Halloween; Christmas; Wedding; Anniversary; Work; School; Weekend; Vacation', 'Activity (+)' : '', 'Sport (+)' : '', 'Season (+)' : '', 'Weather (+)' : 'All-Weather',
    'Is Maternity' : 'No', 'Academic Institution' : '', 'Autographed by' : '', 'Recycled Material' : '', 'Percentage of Recycled Material' : '', 'Warranty Text' : '', 'Warranty URL' : '',
    'Character (+)' : '', 'Brand License (+)' : g_brand, 'Sports League (+)' : '', 'Sports Team (+)' : '', 'Athlete (+)' : '', 'Additional Features (+)' : '', 
    'Additional Product Attribute Name' : '', 'Additional Product Attribute Value' : '', 'Season Year' : '', 'Season Code' : '', 'Variant Group ID' : '', 'Variant Attribute Names (+)' : 'color',
    'Variant Attribute Names 1 (+)' : 'clothingSize', 'Is Primary Variant' : '', 'Swatch Variant Attribute' : 'color', 'Swatch Image URL' : '', 'Fulfillment Lag Time' : 7, 'PPU Quantity of Units' : '',
    'PPU Unit of Measure' : '', 'Contains Electronic Component?' : 'No', 'Contained Battery Type' : '', 'Contains Chemical, Aerosol or Pesticide?' : 'No', 'Multipack Quantity' : '',
    'Ships in Original Packaging' : 'Yes', 'Site Start Date' : '', 'Site End Date' : '', 'Must ship alone?' : 'No', 'Additional Offer Attribute Name' : '', 'Additional Offer Attribute Value' : '',
    'External Product ID Type' : '', 'External Product ID' : '', 'Product Id Update' : 'No', 'SKU Update' : 'No'
}
''' ======= 变量 ========
'''
# 文案异化的相关字段名称(匹配表用pandas读取后的结果)
COL_NAME_INDEX = 'index'
COL_NAME_CORE_WORD = ['Clothing Top Style (+)', 'Sleeve Length Style', 'Clothing Neck Style']
COL_NAME_FEATURE = ['Key Features (+)', 'Key Features 1 (+)', 'Key Features 2 (+)']
COL_NAME_ADORN_WORD = ['Sleeve Style', 'Collar Style', 'Clothing Fit', 'Pattern (+)']
# == 材质成分字段，四组成对 == ↓
COL_NAME_FABNAME_MATCHED = ['Fabric Material Name', 'Fabric Material Name.1', 'Fabric Material Name.2']
COL_NAME_FABPERCENT_MATCHED = ['Fabric Material Percentage', 'Fabric Material Percentage.1', 'Fabric Material Percentage.2']
COL_NAME_FABNAME_ADDITEM = ['Fabric Material Name', 'Fabric Material Name 1', 'Fabric Material Name 2']
COL_NAME_FABPERCENT_ADDITEM = ['Fabric Material Percentage', 'Fabric Material Percentage 1', 'Fabric Material Percentage 2']
# == 材质成分 end == ↑
COL_NAME_IMAGE_URL = ['Main Image URL', 'Additional Image URL (+)', 'Additional Image URL 1 (+)', 'Additional Image URL 2 (+)', 'Additional Image URL 3 (+)', 'Additional Image URL 4 (+)', 'Additional Image URL 5 (+)']
COL_NAME_SWATCH_IMAGE_URL = 'Swatch Image URL'
# 需要忽略不处理的字段: 图片字段，在处理Main Image URL时一并把其它图片url字段也处理了；key feature字段，在处理第1个key feature字段时把后续两个一并处理了；fabric成分字段
COL_NAME_SPU = 'SPU'
COL_NAME_SKU = 'SKU'
COL_NAME_COLOR = 'ColorCode'
COL_NAME_IGNORE = [COL_NAME_SPU, COL_NAME_COLOR] + COL_NAME_IMAGE_URL[1:] + COL_NAME_FEATURE[1:] + COL_NAME_FABNAME_ADDITEM[1:] + COL_NAME_FABPERCENT_ADDITEM[1:]

COL_NAME_ITEM_NAME = 'Product Name'
COL_NAME_PATTERN = 'Pattern (+)'
COL_NAME_GROUP_ID = 'Variant Group ID'
COL_NAME_IS_PRIMARY = 'Is Primary Variant'
CURRENT_SPU = ''
CURRENT_ITEM_NAME = ''
CURRENT_SITE_DESC = ''
CURRENT_PRICE = 0
COL_NAME_SIZE = 'Clothing Size'
COL_NAME_OCCASION = 'Occasion (+)'
spu_size_list = {}
spu_feature_list = {}
spu_color_list = {}
sku_image_url_list = {} # 暂存image url
sku_swatchimage_url_list = {} # 暂存swatch image url

# 字段处理函数集 ==========
# 业务逻辑函数
# 解析feature字段值，有可能是单个单词，也有可能是html的li结构。
# 返回值 所有feature的列表,list类型
def parseFeature(cell_value):
    xml = html.fromstring(cell_value)
    nodes = xml.xpath("//li/text()")
    feature_list = []
    if len(nodes)<=0:
        feature_list.append(cell_value.title())
    else:
        for node in nodes:
            feature_list.append(node.title())
    return feature_list

def genSkuCode(sku_code_src, brand, country):
    sku_code_new = ''
    if sku_code_src.find('|')!=-1:
        segs = sku_code_src.split('|')
        sku_code_bare = segs[-1]
        sku_code_new = f'{country}{util.BRAND_TO_STORE_CODE[g_brand]}|{sku_code_bare}'
    else:
        sku_code_new = f'{country}{util.BRAND_TO_STORE_CODE[g_brand]}|{sku_code_src}'
    return sku_code_new
def genItemName(row_series, colname, brand):
    spu = row_series[COL_NAME_SPU]
    global CURRENT_SPU,CURRENT_ITEM_NAME
    if CURRENT_SPU==spu:
        return CURRENT_ITEM_NAME
    else:
        CURRENT_SPU = spu
        item_name = row_series[colname]
        #print(f'{item_name} -> {brand}')
        word_list = item_name.split(' ')
        if (word_list[0]).upper() in util.BRAND_TO_STORE_CODE: # 如果第1个词是品牌名称
            word_list.pop(0)
        size_group = 'women'
        if (word_list[0]).lower() in util.SIZE_GROUP: # 如果当前第1个词是尺码组(比如women、women's plus等等)
            size_group = word_list[0]
            word_list.pop(0) # 把词单独拿出来
        clothing_type = 'top'
        for word  in word_list:
            lower_word = word.lower()
            if lower_word in util.CLOTHING_TYPE:
                clothing_type = word.title()
        core_word_list = [] # 核心词组
        for col in COL_NAME_CORE_WORD:
            word = str(row_series[col]).title()
            if word!='Nan':
                core_word_list.append(word)
        adorn_word_list = [] # 修饰词组
        for col in COL_NAME_ADORN_WORD:
            word = str(row_series[col]).lower()
            if word!='nan':
                adorn_word_list = adorn_word_list + parseFeature(word)
        item_copywriting,CURRENT_ITEM_NAME = util.alienateItemName(brand, size_group, core_word_list, adorn_word_list, clothing_type)
        return CURRENT_ITEM_NAME
def genPrice(row_series, colname):
    spu = row_series[COL_NAME_SPU]
    global CURRENT_SPU,CURRENT_PRICE
    if spu==CURRENT_SPU:
        return CURRENT_PRICE
    else:
        CURRENT_SPU = spu
        price_src = row_series[colname]
        delta = round(random.uniform(-0.5, 0.5), 2)
        CURRENT_PRICE = price_src + delta
        return CURRENT_PRICE
def genSiteDescription(row_series, colname, df_out):
    spu = row_series[COL_NAME_SPU]
    global CURRENT_SPU,CURRENT_SITE_DESC
    if spu==CURRENT_SPU:
        return CURRENT_SITE_DESC
    else:
        CURRENT_SPU = spu
        template = '''{}<br/>
<br/>
Material: {}<br/>
Size: {}<br/>
Pattern: {}<br/>
Feature: {}<br/>
Occasion: {}'''
        item_name = df_out.loc[row_series[COL_NAME_INDEX], COL_NAME_ITEM_NAME]
        material_str = ''
        for index in range(len(COL_NAME_FABNAME_MATCHED)):
            col_name_fabname = COL_NAME_FABNAME_MATCHED[index]
            col_name_fabpercent = COL_NAME_FABPERCENT_MATCHED[index]
            if str(row_series[col_name_fabname]).lower()!='nan':
                material_str = f'{material_str} {str(row_series[col_name_fabname]).title()}{row_series[col_name_fabpercent]}%'
        size_str = ' /'.join(spu_size_list[spu])
        pattern_str = str(row_series[COL_NAME_PATTERN]).title()
        feature_list = []
        for col in COL_NAME_FEATURE:
            cell_val = str(row_series[col]).lower()
            if cell_val!='nan':
                feature_list = feature_list + parseFeature(cell_val)
        feature_str = ', '.join(feature_list)
        occasion_str = row_series[COL_NAME_OCCASION]
        CURRENT_SITE_DESC = template.format(item_name, material_str, size_str, pattern_str, feature_str, occasion_str)
        return CURRENT_SITE_DESC
def genImageUrl(row_series, index):
    spu = row_series[COL_NAME_SPU]
    color = row_series[COL_NAME_COLOR]
    fullspu_color = f'{g_country}{util.BRAND_TO_STORE_CODE[g_brand]}|{spu}_{color}'
    url = ''
    if fullspu_color in sku_image_url_list:
        if index < len(sku_image_url_list[fullspu_color]):
            url = sku_image_url_list[fullspu_color][index][1]
    else:
        print(f'{spu}.{color}-3x4')
    return url
def genSwatchImageUrl(row_series):
    spu = row_series[COL_NAME_SPU]
    color = row_series[COL_NAME_COLOR]
    fullspu_color = f'{g_country}{util.BRAND_TO_STORE_CODE[g_brand]}|{spu}_{color}'
    url = ''
    if fullspu_color in sku_swatchimage_url_list:
        url = sku_swatchimage_url_list[fullspu_color]
    else:
        print(f'{spu}.{color}-100x100')
    return url
def genFeature(row_series, feature_col_index):
    spu = row_series[COL_NAME_SPU]
    feature_list_src = spu_feature_list[spu]
    new_feature_str = None
    if len(feature_list_src)<=feature_col_index:
        new_feature_str = ''
    else:
        new_feature_str = feature_list_src[feature_col_index]
    return new_feature_str
def genIsPrimary(row_series):
    localSKU,spu,color,size = parseColorSizeCode(row_series[COL_NAME_SKU])
    ret = 'No'
    if spu_color_list[spu][-1]==color and size=='S':  # 把最后一种颜色的S码，作为Primary(Base Item)
        ret = 'Yes'
    return ret


# 映射函数
def skuCodeFunc(df_src, df_out, colname):
    print(f'[func]skuCodeFunc')
    df_out[colname] = df_src[colname].apply(genSkuCode, args=(g_brand, g_country, ))
def pcrIdTypeFunc(df_src, df_out, colname):
    print(f'[func]pcrIdTypeFunc')
    df_out[colname] = 'EAN'
def prdIDFunc(df_src, df_out, colname):
    print(f'[func]prdIDFunc')
def itemNameFunc(df_src, df_out, colname):
    print(f'[func]itemNameFunc')
    df_out[colname] = df_src.apply(genItemName, axis=1, args=(colname, g_brand, ))
def brandFunc(df_src, df_out, colname):
    df_out[colname] = g_brand
def priceFunc(df_src, df_out, colname):
    print(f'[func]priceFunc')
    df_out[colname] = df_src.apply(genPrice, axis=1, args=(colname, ))
def siteDescriptionFunc(df_src, df_out, colname):
    print(f'[func]siteDescriptionFunc')
    df_out[colname] = df_src.apply(genSiteDescription, axis=1, args=(colname, df_out))
def imageUrlFunc(df_src, df_out, colname):
    print(f'[func]imageUrlFunc')
    for index in range(len(COL_NAME_IMAGE_URL)):
        colname = COL_NAME_IMAGE_URL[index]
        df_out[colname] = df_src.apply(genImageUrl, axis=1, args=(index,))
def keyFeatureFunc(df_src, df_out, colname):
    print(f'[func]keyFeatureFunc -> {colname}')
    for index in range(len(COL_NAME_FEATURE)):
        colname = COL_NAME_FEATURE[index]
        df_out[colname] = df_src.apply(genFeature, axis=1, args=(index,))
def swatchImageUrlFunc(df_src, df_out, colname):
    print(f'[func]swatchImageUrlFunc')
    df_out[colname] = df_src.apply(genSwatchImageUrl, axis=1)
def shippingWeightFunc(df_src, df_out, colname):
    df_out[colname ] = df_src['Shipping Weight - Marketplace only']  # match表字段名与add item表字段名不一致
def manufacturerNameFunc(df_src, df_out, colname):
    df_out['Manufacturer Name'] = g_brand
def manufacturerPartNumberFunc(df_src, df_out, colname):
    df_out['Manufacturer Part Number'] = df_out[COL_NAME_SKU]
def seasonYearFunc(df_src, df_out, colname):
    print(f'[func]seasonYearFunc')
    this_year = time.strftime('%Y', time.localtime(time.time()))
    df_out[colname] = this_year
def groupIdFunc(df_src, df_out, colname):
    df_out[COL_NAME_GROUP_ID] = df_src.apply(lambda x:f'{g_country}{util.BRAND_TO_STORE_CODE[g_brand]}|{x[COL_NAME_SPU]}', axis=1)
def isPrimaryFunc(df_src, df_out, colname):
    df_out[colname] = df_src.apply(genIsPrimary, axis=1)
def siteStartDateFunc(df_src, df_out, colname):
    print(f'[func]siteStartDateFunc')
    today_str = time.strftime('%Y-%m-%d', time.localtime(time.time()))
    df_out[colname] = today_str
def siteEndDateFunc(df_src, df_out, colname):
    print(f'[func]siteEndDateFunc')
    enday_str = time.strftime('%Y-%m-%d', time.localtime(time.time()+3122064000))
    df_out[colname] = enday_str
def fabricMaterialFunc(df_src, df_out, colname):
    print(f'[func]fabricMaterialFunc')
    for index in range(len(COL_NAME_FABNAME_MATCHED)):
        colname_fabname_additem = COL_NAME_FABNAME_ADDITEM[index]
        colname_fabname_matched = COL_NAME_FABNAME_MATCHED[index]
        colname_percent_additem = COL_NAME_FABPERCENT_ADDITEM[index]
        colname_percent_matched = COL_NAME_FABPERCENT_MATCHED[index]
        df_out[colname_fabname_additem] = df_src[colname_fabname_matched]
        df_out[colname_percent_additem] = df_src[colname_percent_matched]
def normalColumnsFiller(row_series, colname):
    ret = None
    if colname in row_series and (not pd.isnull(row_series[colname])): # 匹配表包含该字段且不为空
        # print(f'[normalColumnsFiller::<{colname}>]Get Matched:{row_series[colname]}')
        ret = row_series[colname]
    else:
        # print(f'[normalColumnsFiller::<{colname}>]Get Dict:{col_names_additem[colname]}')
        ret = col_names_additem[colname]
    return ret


# 需要单独处理逻辑的字段及对应逻辑函数
col_need_change = {
    COL_NAME_SKU : skuCodeFunc,
    'Product ID Type' : pcrIdTypeFunc,
    'Product ID' : prdIDFunc,
    COL_NAME_ITEM_NAME : itemNameFunc,
    'Brand' : brandFunc,
    'Selling Price' : priceFunc,
    'Shipping Weight (lbs)' : shippingWeightFunc,
    'Site Description' : siteDescriptionFunc,
    'Main Image URL' : imageUrlFunc,
    COL_NAME_SWATCH_IMAGE_URL : swatchImageUrlFunc,
    'Key Features (+)' : keyFeatureFunc,
    'Fabric Material Name' : fabricMaterialFunc,
    'Manufacturer Name' : manufacturerNameFunc,
    'Manufacturer Part Number' : manufacturerPartNumberFunc,
    'Season Year' : seasonYearFunc,
    COL_NAME_GROUP_ID : groupIdFunc,
    COL_NAME_IS_PRIMARY : isPrimaryFunc,
    'Site Start Date' : siteStartDateFunc,
    'Site End Date' : siteEndDateFunc
}

df_matched = pd.read_excel(f'{g_basedir}/matched_item.xlsx') # 读取匹配资料表
df_matched[COL_NAME_INDEX] = df_matched.index
df_matched = df_matched.assign(SPU=df_matched[COL_NAME_SKU].apply( lambda x:( parseColorSizeCode(x) )[1] ) )
df_matched = df_matched.assign(ColorCode=df_matched[COL_NAME_SKU].apply(lambda x:(parseColorSizeCode(x))[2]))
''' ===============================================
# 资料预处理
'''
# 每个SPU的相关数据集合...
for index,row in df_matched.iterrows():
    sku = str(row[COL_NAME_SKU])
    localSKU,spu,color,size = parseColorSizeCode(sku)
    # 尺码list
    if spu not in spu_size_list:
        spu_size_list[spu] = []
    size_str = str(row[COL_NAME_SIZE])
    if size_str not in spu_size_list[spu]:
        spu_size_list[spu].append(size_str)
    # 颜色代码 list
    if spu not in spu_color_list:
        spu_color_list[spu] = []
    spu_color_list[spu].append(color)
    # feature list
    feature_list = []
    for col in COL_NAME_FEATURE:
        word = str(row[col]).lower()
        if word!='nan':
            feature_list = feature_list + parseFeature(word)
    feature_list = util.resizeAndShuffle(feature_list)
    spu_feature_list[spu] = feature_list

#  图片URL处理
imgSrcPath = f'{g_basedir}/imgurl.xlsx'
imgWB = openpyxl.load_workbook(imgSrcPath)
imgWS = imgWB.active
# 遍历取值
row_iter = imgWS.iter_rows(values_only=True)
# print(row_iter)
for row in row_iter:
    sku = row[2]
    url = row[4]
    try:
        spu, color, order_or_size = parseImageFileName(sku)
        full_spu = f'{g_country}{util.BRAND_TO_STORE_CODE[g_brand]}|{spu}' # 加上区域品牌前缀
        #print(spu, color, order_or_size)
        spucolor = f"{full_spu}_{color}"
        if int(order_or_size)==-1:
            sku_swatchimage_url_list[spucolor] = url
        else:
            if spucolor not in sku_image_url_list:
                sku_image_url_list[spucolor] = [(int(order_or_size), url)]
            else:
                sku_image_url_list[spucolor].append((int(order_or_size), url))        
    except Exception as e:
        print(e)

# 对主图排序
for k,v in sku_image_url_list.items():
    v.sort(key=lambda x:x[0])

df_add = pd.DataFrame(columns=col_names_additem)
df_add[COL_NAME_INDEX] = df_matched[COL_NAME_INDEX]  # 添加索引列
df_add[COL_NAME_SPU] = df_matched[COL_NAME_SPU] # 添加spu列
df_add[COL_NAME_COLOR] = df_matched[COL_NAME_COLOR] # 添加颜色代码列
col_names_matched = list(df_matched.columns)
for col in col_names_additem:
    if col in COL_NAME_IGNORE:
        continue
    elif col in col_need_change:
        col_need_change[col](df_matched, df_add, col)
    else:
        df_add[col] = df_matched.apply(normalColumnsFiller, axis=1, args=(col,))

df_add.to_excel(f'{g_basedir}/add_item.xlsx', index=False)